﻿CREATE PROCEDURE [dbo].[report_products_in_stock]
	(
	@p_nsi_organization_id int,
	@p_xml_products xml
	)
AS
BEGIN
	SET NOCOUNT ON;

	declare @docHandle int
	EXEC sp_xml_preparedocument @docHandle OUTPUT, @p_xml_products

	SELECT pu.NAME as PRODUCT_NAME, COUNT(pu.NAME) as PRODUCT_COUNT, o.SHORT_NAME as ORGANIZATION_NAME, c.NUMBER as CONTRACT_NUM,
			SUM(pu.PAY_PER)/COUNT(pu.NAME) as PAY_PER, (CASE cp.IS_AGREED  WHEN 1 THEN 'ДА' ELSE 'НЕТ' END) as IS_AGREE
	FROM VIEW_SEARCH_P_U_FOR_FORMS as pu
	INNER JOIN PID on PID.ID = pu.PID
	INNER JOIN PRODUCT_COST as pc ON pc.PRODUCT_UNIT_ID = pu.ID
	INNER JOIN COST_PROTOCOL as cp ON cp.ID = pc.COST_PROTOCOL_ID
	INNER JOIN CONTRACT as c ON c.ID = PID.CONTR_ID
	LEFT JOIN NSI_ORGANIZATION as o ON o.ID = c.NSI_ORGANIZATION_ID
	WHERE pu.NSI_PRODUCT_STATE_ID = 3
	AND
	(
		(@p_nsi_organization_id is null)
		OR
		(c.NSI_ORGANIZATION_ID = @p_nsi_organization_id)
	)
	AND
	(
		(@p_xml_products is null)
		OR
		(
			pu.NSI_PRODUCT_ID IN ( SELECT x.ID FROM OPENXML(@docHandle, N'/products/product') WITH (id int) as x )
		)
	)
	GROUP BY o.SHORT_NAME, c.NUMBER, pu.NAME, cp.IS_AGREED 
	
	EXEC sp_xml_removedocument @docHandle
END